import copy
import time
import traceback
from typing import List

import aiomysql
from fastapi import APIRouter
from pymysql import escape_string
from re_common.baselibrary.utils.core.requests_core import MsgCode

from apps.core.m_route import ContextIncludedRoute
from apps.core.return_info import ReturnInfo, SUCCESS, FAILED
from apps.crawler_platform.core_api.models import AddSqlModel, UpdateSqlModel, DeleteSqlModel, QuerySqlModel, \
    AddManyModel, ExecSqlModel, ExecManySqlModel, UpdateSqlModelCtrl
from apps.crawler_platform.core_platform.g_model import InputPlatformModel

router = APIRouter(route_class=ContextIncludedRoute)
from warnings import filterwarnings

filterwarnings('ignore', category=aiomysql.Warning)


def update_dicts(dicts):
    strings = ""
    lists = []
    for k, v in dicts.items():
        strings = strings + k + "=%s" + ","
        lists.append(v)
    strings = strings.rstrip(',')
    return strings, lists


def update_dicts2(dicts):
    """
    不适用%s占位符号的预计 比如 stat=stat+1 这样的加一操作
    :param dicts:
    :return:
    """
    strings = ""
    for k, v in dicts.items():
        strings = strings + k + "=" + f"{v},"
    strings = strings.rstrip(',')
    return strings


def where_dicts(items):
    if isinstance(items, dict):
        strings = ""
        lists = []
        for k, v in items.items():
            strings = strings + k + "=%s " + "and "
            lists.append(v)
        strings = strings.rstrip('and ')
        return strings, lists
    else:
        strings = ""
        lists = []
        description = ""
        for item in items:
            operator = item.operator
            k = item.key
            v = item.value
            description = item.description
            strings = strings + k + operator + "%s " + description + " "
            lists.append(v)
        strings = strings.rstrip(description + ' ')
        return strings, lists


def order_dicts(dicts):
    strings = ""
    for k, v in dicts.items():
        v = escape_string(v)
        if v == "RAND":
            strings = strings + "RAND(),"
        else:
            strings = strings + k + f" {v},"
    strings = strings.rstrip(',')
    return strings


#
# def solve_sql(strings, lists):
#     """
#         ZCN:仅对 含failcount的字段使用
#         :return:
#     """
#     deep_list = copy.deepcopy(lists)
#     for i in lists:
#         if "failcount" in str(i):
#             p = i.split("+")[0]
#             k = p + "=%s"
#             k2 = p + "=" + p + "+1"
#             strings = strings.replace(k, k2)
#             deep_list.remove(i)
#     return strings, deep_list


@router.post("/sql_api/exec_sql")
async def exec_sql(inputs: InputPlatformModel[ExecSqlModel]):
    """
   查询sql 直接传入sql语句
   field:*或者rawid,stat,uptime或者count(*)
   order:{"rawid":"desc","rand":"RAND","uptime":"ASC"}
   limit:0
   :return:
   """
    return_info = ReturnInfo()
    sql = ""
    try:
        sql = inputs.data.sql
        is_dicts = inputs.data.is_dicts
        cursors = ()
        if is_dicts:
            cursors = (aiomysql.DictCursor,)
        from main import app
        pool = app.state.pool
        async with pool.acquire() as conn:
            async with conn.cursor(*cursors) as cur:
                await cur.execute(sql)
                await conn.commit()
                row = await cur.fetchall()

        return_info.status = SUCCESS
        return_info.msg_code = 200
        return_info.msg = "执行query sql成功;"
        return_info.data = row
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = 400
        return_info.msg = "执行query sql失败;" + sql
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/exec_many_sql")
async def query_sql(inputs: InputPlatformModel[ExecManySqlModel]):
    """
   执行多条数据的sql 直接传入sql语句
   field:*或者rawid,stat,uptime或者count(*)
   order:{"rawid":"desc","rand":"RAND","uptime":"ASC"}
   limit:0
   :return:
   """
    return_info = ReturnInfo()
    sql = ""
    try:
        sql = inputs.data.sql
        value_list = inputs.data.value_list
        from main import app
        pool = app.state.pool
        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(sql, value_list)
                await conn.commit()
                row = await cur.fetchall()
        return_info.status = SUCCESS
        return_info.msg_code = 200
        return_info.msg = "执行query sql成功;"
        return_info.data = row
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = 400
        return_info.msg = "执行query sql失败;" + sql
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/query_sql")
async def query_sql(inputs: InputPlatformModel[QuerySqlModel]):
    """
   查询sql
   field:*或者rawid,stat,uptime或者count(*)
   order:{"rawid":"desc","rand":"RAND","uptime":"ASC"}
   limit:0
   :return:
   """
    return_info = ReturnInfo()
    sql = ""
    try:
        table_name = inputs.data.table
        field = inputs.data.field
        where, lists = where_dicts(inputs.data.where)
        order = order_dicts(inputs.data.order)
        limit = inputs.data.limit
        sql = f" SELECT {field} from {table_name} where {where} order by {order} limit {limit}"
        from main import app
        pool = app.state.pool
        async with pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                await cur.execute(sql, lists)
                row = await cur.fetchall()
        return_info.status = SUCCESS
        return_info.msg_code = 200
        return_info.msg = "执行query sql成功;"
        return_info.data = row
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = 400
        return_info.msg = "执行query sql失败;" + sql
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/delete_sql")
async def delete_sql(inputs: InputPlatformModel[DeleteSqlModel]):
    """
    删除sql
    :return:
    """
    return_info = ReturnInfo()
    try:
        table_name = inputs.data.table
        where, lists = where_dicts(inputs.data.where)
        sql = f"delete from {table_name} where {where}"
        from main import app
        pool = app.state.pool
        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(sql, lists)
                await conn.commit()
        return_info.status = SUCCESS
        return_info.msg_code = 200
        return_info.msg = "执行delete sql成功"
        return_info.data = sql
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = 400
        return_info.msg = "执行delete sql失败"
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/add_sql")
async def add_sql(inputs: InputPlatformModel[AddSqlModel]):
    """
    添加sql
    :return:
    """
    return_info = ReturnInfo()
    sql = ""
    try:
        table_name = inputs.data.table
        keys = ""
        list_v = []
        list_f = []
        for k, v in inputs.data.feild_dicts.items():
            keys = keys + f"`{k}`,"
            list_f.append("%s")
            list_v.append(v)

        keys = keys.rstrip(",")
        list_f = ",".join(list_f)
        insert_s = inputs.data.insert
        sql = f" {insert_s} {table_name}({keys}) values ({list_f})"
        if inputs.data.ndku_feild:
            ndku_str = ""
            for k, v in inputs.data.ndku_feild.items():
                ndku_str = ndku_str + f"`{k}`=%s,"
                list_v.append(v)
            ndku_str = ndku_str.rstrip(",")
            sql = sql + f" ON DUPLICATE KEY UPDATE {ndku_str}"
        sql = sql + ";"
        from main import app
        pool = app.state.pool
        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(sql, list_v)
                await conn.commit()
        return_info.status = SUCCESS
        return_info.msg_code = 200
        return_info.msg = "执行insert sql成功"
        return_info.data = sql
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_INSERT_ERROR
        return_info.msg = "执行insert sql失败;" + sql
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/add_many_sql")
async def add_many_sql(inputs: InputPlatformModel[AddManyModel]):
    """
    添加sql
    :return:
    """
    return_info = ReturnInfo()
    sql = ""
    value_list = []
    try:
        table_name = inputs.data.table
        keys = ",".join(inputs.data.feild_list)
        list_f = ",".join(['%s'] * len(inputs.data.feild_list))
        insert_s = inputs.data.insert
        sql = f"{insert_s} {table_name}({keys}) values ({list_f})"
        value_list = inputs.data.value_list
        from main import app
        pool = app.state.pool
        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.executemany(sql, value_list)
                await conn.commit()
        return_info.status = SUCCESS
        return_info.msg_code = 200
        return_info.msg = "执行insert many sql成功"
        return_info.data = sql
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_INSERT_ERROR
        return_info.msg = "执行insert many sql失败;" + sql + ";" + str(value_list)
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/update_sql")
async def update_sql(inputs: InputPlatformModel[UpdateSqlModel]):
    """
    添加sql
    :return:
    """
    return_info = ReturnInfo()
    sql = ""
    list_up = ""
    try:
        table_name = inputs.data.table
        up, list_up = update_dicts(inputs.data.update)
        where, lists = where_dicts(inputs.data.where)
        up_no = update_dicts2(inputs.data.update_no_placeholder)
        # # 处理failcount+1问题
        # up, list_up = solve_sql(up, list_up)
        if up_no:
            up = up + "," + up_no
        up = up.lstrip(",").rstrip(",")
        sql = f"update {table_name} set {up} where {where};"
        list_up.extend(lists)
        from main import app
        pool = app.state.pool
        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(sql, list_up)
                await conn.commit()
        return_info.status = SUCCESS
        return_info.msg_code = 200
        return_info.msg = "执行update sql成功"
        return_info.data = sql
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_UPDATE_ERROR
        return_info.msg = "执行update sql失败;" + sql + ";" + "list:" + str(list_up)
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/update_control_sql")
async def update_control_sql(inputs: InputPlatformModel[UpdateSqlModelCtrl]):
    """
    用于控制库的下载
    单task_name, task_switch为1全开,为0全关
    加task_tag, task_switch为1为开, 为0为关
    :return:
    """
    return_info = ReturnInfo()
    table_name = "taskinfo_save_setting"
    task_switch = inputs.data.task_switch
    task_name = inputs.data.task_name
    task_tag = inputs.data.task_tag
    if task_switch == "1" and task_tag == "":
        sql = f"update {table_name} set task_state = 1 where task_name = '{task_name}'"
    elif task_switch == "1" and task_tag != "":
        sql = f"update {table_name} set task_state = 1, tag_state = 1 where task_name = '{task_name}' and task_tag = '{task_tag}'"
    elif task_switch == "0" and task_tag == "":
        sql = f"update {table_name} set task_state = 0 where task_name = '{task_name}'"
    elif task_switch == "0" and task_tag != "":
        sql = f"update {table_name} set task_state = 1, tag_state = 0 where task_name = '{task_name}' and task_tag = '{task_tag}'"
    else:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_UPDATE_ERROR
        return_info.msg = "执行update sql失败, 因为参数传递错误"
        return_info.data = [task_name, task_tag, task_switch]
        return return_info.todict()
    try:
        from main import app
        pool = app.state.pool
        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(sql)
                await conn.commit()
        return_info.status = SUCCESS
        return_info.msg_code = 200
        return_info.msg = "执行update sql成功"
        return_info.data = sql
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_UPDATE_ERROR
        return_info.msg = "执行update sql失败;"
        return_info.data = traceback.format_exc()
        return return_info.todict()
